package pack.be;

import java.sql.*;
import java.util.Properties;
import java.util.Vector;

public class ConnectionPostgre {
	
	private static Connection con;
	//Properties props = new Properties();
	private static String username;
	
	public ConnectionPostgre() {
		
	}
	
	public void connection() throws Exception{
		Class.forName("org.postgresql.Driver");
		String url;
		
		url = "jdbc:postgresql://10.0.2.2:5432/bdd";
		con = DriverManager.getConnection(url,"postgres","hello");
		
		if (con == null)
		    System.out.println("We should never get here.");
	}
	
	public void deconnection() throws SQLException{
        if (con != null)
        {
            con.close();
        }
        System.out.println("Ha habido algun error al cerrar la DB");
	}
	
	public String[] listeProjets(String utilisateur) throws SQLException{
		Statement st;
		ResultSet rs;
		String sql = "SELECT P.nom_Projet, P.idProjet FROM Projet P WHERE idProjet = " +
				"(SELECT Part.id_Projet FROM Participe_Projet Part WHERE Part.id_Utilisateur = " +
				"(SELECT U.idUtilisateur FROM utilisateur U WHERE U.nom= 'Pierre'));";
		String resultat[];
		Vector <String> res = new Vector<String>();
		
		st = con.createStatement();		
		rs = st.executeQuery(sql);
		
		while( rs.next() ){
			res.add(rs.getString(1));
			res.add(String.valueOf(rs.getInt(2)));
		}
		
		resultat = new String[res.size()];
		for(int i = 0; i<res.size(); i++){
			resultat[i]=res.elementAt(i);
		}
		
		return resultat;
	}
	
	public String[] indicateurProjet(int idProjet) throws SQLException{
		Statement st;
		ResultSet rs;
		String sql = "SELECT K.idKPI, K.id_text, K.date_Generation FROM KPI K WHERE K.idProjet = 1;";
		String resultat[] = new String[100];
		String stock;
		int cont = 0;
		

		Vector <String> res = new Vector<String>();
		
		st = con.createStatement();		
		rs = st.executeQuery(sql);
		
		while( rs.next() ){
			res.add(rs.getString(2));
		}
		
		resultat = new String[res.size()];
		for(int i = 0; i<res.size(); i++){
			resultat[i]=res.elementAt(i);
		}
		
		return resultat;		
	}
	
	public String[] affichageProjet(String indicator) throws SQLException{
		Statement st;
		ResultSet rs;
		//ou est mon entre?
		String sql = "SELECT Z.titre, Z.objectif, Z.regle_Calcul, Z.couleur, D.periode, D.quantite, " +
				"D.pourcentage, D.note, D.tendance FROM Donnee D, " +
				"(SELECT KA.titre, KA.objectif, KA.regle_Calcul, Y.couleur, Y.idKPI FROM KPI_Affichage KA, " +
				"( SELECT Kt.idAffichage, X.couleur, X.idKPI FROM KPI_Tableau Kt, " +
				"( SELECT Dt.idTableau, Dt.couleur, W.idKPI FROM Data_T Dt," +
				"( SELECT K.id_text, K.idKPI FROM KPI K  WHERE K.id_text= '"+indicator+"' )  " +
				"as W WHERE Dt.nomDatat= W.id_Text) as X WHERE Kt.idTab=X.idTableau ) " +
				"as Y WHERE KA.idAff = Y.idAffichage ) as Z WHERE D.idKPI= Z.idKPI ;";
		
		String resultat[] = new String[100];
		String stock;
		int cont = 0;
		

		Vector <String> res = new Vector<String>();
		
		st = con.createStatement();		
		rs = st.executeQuery(sql);
		
		while( rs.next() ){
			res.add(rs.getString(6));
		}
		
		resultat = new String[res.size()];
		for(int i = 0; i<res.size(); i++){
			resultat[i]=res.elementAt(i);
		}
		
		return resultat;			
	}
	
	public String[] affichageGraphicProjet(String indicator) throws SQLException{
		Statement st;
		ResultSet rs;
		//ou est mon entre?
		String sql = "SELECT Z.titre, Z.idKPI, Z.objectif, Z.regle_Calcul, Z.titre_Data, Z.echelle_Min, " +
				"Z.echelle_Max, Z.afficher_Legende, Z.nb_Periode, Z.periode_Unite, Z.periode_Min, Z.couleur," +
				"Z.graphe_style, Z.graphe_type, D.periode, D.quantite, D.pourcentage, D.note, D.tendance " +
				"FROM Donnee D, " +
				"(SELECT KA.titre, KA.objectif, KA.regle_Calcul, Y.titre_Data, Y.echelle_Min, Y.echelle_Max," +
				" Y.afficher_Legende, Y.nb_Periode, Y.periode_Unite, Y.periode_Min, Y.couleur, " +
				"Y.graphe_style, Y.graphe_type,Y.idKPI FROM KPI_Affichage KA, " +
				"( SELECT Kg.idAffichage, Kg. titre_Data, Kg.echelle_Min, Kg.echelle_Max, " +
				"Kg.afficher_Legende, Kg.nb_Periode, Kg.periode_Unite, Kg.periode_Min, X.couleur, " +
				"X.graphe_style, X.graphe_type, X.idKPI FROM KPI_Graphe Kg, " +
				"( SELECT Dg.idGraphe, Dg.couleur, Dg.graphe_style, Dg.graphe_type, W.idKPI FROM Data_G Dg," +
				"( SELECT K.id_text, K.idKPI FROM KPI K WHERE K.id_text= '"+indicator+"') " +
				"as W WHERE Dg.nomDatag= W.id_Text) as X WHERE Kg.idgraphe=X.idGraphe) " +
				"as Y WHERE KA.idAff = Y.idAffichage) as Z WHERE D.idKPI= Z.idKPI;";
		
		String resultat[] = new String[100];
		String stock;
		int cont = 0;
		

		Vector <String> res = new Vector<String>();
		
		st = con.createStatement();		
		rs = st.executeQuery(sql);
		
		while( rs.next() ){
			res.add(rs.getString(16));
		}
		
		resultat = new String[res.size()];
		for(int i = 0; i<res.size(); i++){
			resultat[i]=res.elementAt(i);
		}
		
		return resultat;			
	}
    
	public String prueba(){
		Statement st;
		String sql = "SELECT nom, prenom FROM utilisateur WHERE idUtilisateur ="+"1"+";";
		String nombre="";
    	String apellido="";
    	
		try {
			st = con.createStatement();
		
			ResultSet rs = st.executeQuery(sql);
		
			while( rs.next() ){
			//Columnsarecanbereferencedbyname.
				apellido = rs.getString(1);
                nombre = rs.getString(2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return nombre+" "+apellido;
    }

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		ConnectionPostgre.username = username;
	}   
}